if exists (select 1
          from sysobjects
          where  id = object_id('deleteLeaveProc')
          and type in ('P','PC'))
DROP PROCEDURE deleteLeaveProc
GO
create PROCEDURE [dbo].[deleteLeaveProc]
  @order_id AS varchar(10) ,
  @remove_user AS varchar(20) ,
  @return_remark AS varchar(max)
AS
BEGIN
	-- 针对审批完成的单子进行取消操作，需要重新计算考勤。

	DECLARE @status int --单子的审批状态（3：审批通过，4：自动审批）
	DECLARE @BeginDate DATE --单子开始日期
	DECLARE @EndDate DATE --单子结束日期
	DECLARE @users VARCHAR(MAX) = '' --申请的人Id字符串（xxx,xxx,xxx,xxx）

	begin try
		begin tran
			--1更新单子状态为-1 is_del（0:正常 ，-1：取消）并记录取消信息
			 update bd_leave_order
          set is_del = -1,
            remove_user = @remove_user,
            return_remark = @return_remark,
            remove_time = getdate()
          where order_id = @order_id;

			--2更新明细表状态whether_validity (1:正常。2：取消)
			update lr_kqingj
				set whether_validity = 2
				 where order_id = @order_id;

			--3重新计算考勤
			select @status = status,
							@BeginDate = start_date ,
							@EndDate = end_date
			from bd_leave_order
			where order_id = @order_id;
			if (@status=3 or @status=4)
				BEGIN
					SELECT
							@users = @users + CAST(u.user_serial AS VARCHAR(100)) + ','
						FROM (
								select DISTINCT user_serial from lr_kqingj where order_id = @order_id
						) u

					SET @users = SUBSTRING(@users,0, LEN(@users))
					EXEC bd_proc_timecard_extend @BeginDate, @EndDate, @users,6
				END
			commit tran
	end try
	begin catch
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
				print @errStr
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
	end catch
END